﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class AccessorialChargesDA : IAccessorialChargesDA
    {
        private string ACCESSORIAL_CHARGES_ID = "@ACCESSORIAL_CHARGES_ID";
        private string ACCESSORIAL_ID = "@ACCESSORIAL_ID";
        private string ACCESSORIAL_TYPE = "@ACCESSORIAL_TYPE";
        private string BILLED_UNITS = "@BILLED_UNITS";
        private string BILLING_AMOUNT = "@BILLING_AMOUNT";
        private string BILLING_OBJECT = "@BILLING_OBJECT";
        private string BILLING_OBJECT_ID = "@BILLING_OBJECT_ID";
        private string BILLING_OBJECT_NAME = "@BILLING_OBJECT_NAME";
        private string BILLING_QTY = "@BILLING_QTY";
        private string CHARGE_INVOCATION = "@CHARGE_INVOCATION";
        private string CHARGE_PER_UNIT = "@CHARGE_PER_UNIT";
        private string CHARGE_TYPE = "@CHARGE_TYPE";
        private string CHARGE_UNIT = "@CHARGE_UNIT";
        private string COST_TYPE = "@COST_TYPE";
        private string CREATED_BY = "@CREATED_BY";
        private string DEBIT = "@DEBIT";
        private string DESCR = "@DESCR";
        private string DUE_FROM_TO = "@DUE_FROM_TO";
        private string GENERAL_LEDGER_ID = "@GENERAL_LEDGER_ID";
        private string IS_CHECKED = "@IS_CHECKED";
        private string LOT = "@LOT";
        private string ORDER_ID = "@ORDER_ID";
        private string ORDER_LINE_ID = "@ORDER_LINE_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string QTY = "@QTY";
        private string RATE_ID = "@RATE_ID";
        private string REMARK = "@REMARK";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_ACCESSORIAL_CHARGES_ID_UNIQUE = " SELECT COUNT(1) FROM @_@ACCESSORIAL_CHARGES WHERE ACCESSORIAL_CHARGES_ID = @ACCESSORIAL_CHARGES_ID ";
        private string SQL_DELETE_ACCESSORIAL_CHARGES = " DELETE FROM @_@ACCESSORIAL_CHARGES WHERE ACCESSORIAL_CHARGES_ID = @ACCESSORIAL_CHARGES_ID ";
        private string SQL_INSERT_ACCESSORIAL_CHARGES = " INSERT INTO @_@ACCESSORIAL_CHARGES ( WH_ID, ACCESSORIAL_CHARGES_ID, ORDER_ID, ORDER_LINE_ID, OWNER_ID, SKU_ID, QTY, LOT, RATE_ID, GENERAL_LEDGER_ID, DEBIT, BILLED_UNITS, ACCESSORIAL_ID, TAX_GROUP_ID, CHARGE_UNIT, CHARGE_PER_UNIT, CHARGE_INVOCATION, ACCESSORIAL_TYPE, DESCR, IS_CHECKED, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,COST_TYPE,BILLING_OBJECT,BILLING_OBJECT_ID,BILLING_OBJECT_NAME,DUE_FROM_TO,BILLING_QTY,TOTAL_NET_WEIGHT,TOTAL_NET_VOLUME,REMARK,CHARGE_TYPE,BILLING_AMOUNT ) VALUES ( @WH_ID, @ACCESSORIAL_CHARGES_ID, @ORDER_ID, @ORDER_LINE_ID, @OWNER_ID, @SKU_ID, @QTY, @LOT, @RATE_ID, @GENERAL_LEDGER_ID, @DEBIT, @BILLED_UNITS, @ACCESSORIAL_ID, @TAX_GROUP_ID, @CHARGE_UNIT, @CHARGE_PER_UNIT, @CHARGE_INVOCATION, @ACCESSORIAL_TYPE, @DESCR, @IS_CHECKED, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@COST_TYPE,@BILLING_OBJECT,@BILLING_OBJECT_ID,@BILLING_OBJECT_NAME,@DUE_FROM_TO,@BILLING_QTY,@TOTAL_NET_WEIGHT,@TOTAL_NET_VOLUME,@REMARK,@CHARGE_TYPE,@BILLING_AMOUNT )  ";
        private string SQL_SELECT_ALL_ACCESSORIAL_CHARGES = " SELECT  ROWID, WH_ID, ACCESSORIAL_CHARGES_ID, ORDER_ID, ORDER_LINE_ID, OWNER_ID, SKU_ID, QTY, LOT, RATE_ID, GENERAL_LEDGER_ID, DEBIT, BILLED_UNITS, ACCESSORIAL_ID, TAX_GROUP_ID, CHARGE_UNIT, CHARGE_PER_UNIT, CHARGE_INVOCATION, ACCESSORIAL_TYPE, DESCR, IS_CHECKED, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,COST_TYPE,BILLING_OBJECT,BILLING_OBJECT_ID,BILLING_OBJECT_NAME,DUE_FROM_TO,BILLING_QTY,TOTAL_NET_WEIGHT,TOTAL_NET_VOLUME,REMARK,CHARGE_TYPE,BILLING_AMOUNT,CASE BILLING_OBJECT WHEN 'OWNER' THEN '货主' WHEN 'VENDOR' THEN '供应商' WHEN 'CUSTOMER' THEN '客户' WHEN 'CARRIER' THEN '承运商' WHEN 'BILLINGENTITY' THEN '计费实体' END AS BILLING_OBJECT_DESCR FROM  @_@ACCESSORIAL_CHARGES ";
        private string SQL_SELECT_ALL_IN_BOUND = " SELECT  R.ROWID AS ROWID, R.WH_ID AS WH_ID, R.RECEIPT_ID AS ORDER_ID, T2.[TEXT] AS TYPE, R.OWNER_ID AS OWNER_ID, T.[TEXT] AS STATUS,R.CREATED_DATE,R.CREATED_BY FROM  @_@RECEIPT R  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = R.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = R.RECEIPT_TYPE AND SCD2.SYS_CODE_TYPE = 'ReceiptType' LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+R.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'ReceiptType.'+R.RECEIPT_TYPE = T2.ITEM_ID  AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_MOVEMENT = " SELECT  R.ROWID, R.WH_ID, R.MOVEMENT_ID AS ORDER_ID, R.OWNER_ID, R.MOVEMENT_TYPE AS TYPE,T.[TEXT] AS STATUS,R.CREATED_BY, R.CREATED_DATE   FROM  @_@MOVEMENT R  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = R.STATUS AND SCD.SYS_CODE_TYPE = 'MovementStatus'  LEFT JOIN sysadm.TRANSLATION T ON 'MovementStatus.'+R.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY =@LANGUAGE  INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_MOVEMENT_DETAIL = " SELECT  RD.ROWID, RD.WH_ID, RD.MOVEMENT_ID AS ORDER_ID, RD.LINE_ID, RD.OWNER_ID,RD.SKU_ID, SKU.DESCR AS SKU_DESCR,'' AS ALT_SKU_ID,T.[TEXT] AS STATUS, RD.UOM_ID, RD.PACK_ID, RD.LOT,RD.QTY  FROM  @_@MOVEMENT_DETAIL RD   LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.STATUS AND SCD.SYS_CODE_TYPE = 'MovementStatus'    LEFT JOIN sysadm.TRANSLATION T ON 'MovementStatus.'+RD.STATUS = T.ITEM_ID   AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE   LEFT JOIN @_@SKU SKU ON RD.OWNER_ID = SKU.OWNER_ID AND RD.SKU_ID = SKU.SKU_ID   LEFT JOIN @_@MOVEMENT R ON RD.MOVEMENT_ID = R.MOVEMENT_ID   INNER JOIN SYSADM.USER_OWNER UO ON RD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_ORDER_DETAILA = " SELECT RD.ROWID, RD.WH_ID, RD.ORDER_ID,  RD.LINE_ID, RD.SKU_ID, RD.ALT_SKU_ID, RD.OWNER_ID,SKU.DESCR AS SKU_DESCR, RD.UOM_ID, RD.PACK_ID,T.[TEXT] AS STATUS, RD.LOT,RD.ORIGINAL_QTY AS QTY  FROM @_@ORDER_DETAIL RD  LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = RD.OWNER_ID AND SKU.SKU_ID = RD.SKU_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+RD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON RD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_OUT_BOUND = " SELECT  R.ROWID AS ROWID,R.WH_ID AS WH_ID, R.ORDER_ID AS ORDER_ID, R.OWNER_ID AS OWNER_ID,T2.[TEXT] AS TYPE, T.[TEXT] AS STATUS,R.CREATED_DATE,R.CREATED_BY  FROM  @_@ORDERS R LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = R.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = R.TYPE AND SCD2.SYS_CODE_TYPE = 'OrderType' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+R.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'OrderType.'+R.TYPE = T2.ITEM_ID  \t\t\tAND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE LEFT JOIN @_@INTRANSIT I ON R.INTRANSIT_ID = I.INTRANSIT_ID  INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_RECEIPT_DETAIL = " SELECT  RD.ROWID, RD.WH_ID, RD.RECEIPT_ID AS ORDER_ID, RD.RECEIPT_LINE_ID, RD.OWNER_ID,RD.SKU_ID, SKU.DESCR AS SKU_DESCR, RD.ALT_SKU_ID,T.[TEXT] AS STATUS, RD.UOM_ID, RD.PACK_ID, RD.TO_LOT,RD.EXPECTED_QTY AS QTY  FROM  @_@RECEIPT_DETAIL RD  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+RD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN @_@SKU SKU ON RD.OWNER_ID = SKU.OWNER_ID AND RD.SKU_ID = SKU.SKU_ID  LEFT JOIN @_@RECEIPT R ON RD.RECEIPT_ID = R.RECEIPT_ID  INNER JOIN SYSADM.USER_OWNER UO ON RD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_UPDATE_ACCESSORIAL_CHARGES = " UPDATE @_@ACCESSORIAL_CHARGES SET WH_ID = @WH_ID, ORDER_ID = @ORDER_ID, ORDER_LINE_ID = @ORDER_LINE_ID, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, QTY = @QTY, LOT = @LOT, RATE_ID = @RATE_ID, GENERAL_LEDGER_ID = @GENERAL_LEDGER_ID, DEBIT = @DEBIT, BILLED_UNITS = @BILLED_UNITS, ACCESSORIAL_ID = @ACCESSORIAL_ID, TAX_GROUP_ID = @TAX_GROUP_ID, CHARGE_UNIT = @CHARGE_UNIT, CHARGE_PER_UNIT = @CHARGE_PER_UNIT, CHARGE_INVOCATION = @CHARGE_INVOCATION, ACCESSORIAL_TYPE = @ACCESSORIAL_TYPE, DESCR = @DESCR, IS_CHECKED = @IS_CHECKED, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),COST_TYPE = @COST_TYPE,BILLING_OBJECT = @BILLING_OBJECT,BILLING_OBJECT_ID = @BILLING_OBJECT_ID,BILLING_OBJECT_NAME = @BILLING_OBJECT_NAME,DUE_FROM_TO = @DUE_FROM_TO,BILLING_QTY = @BILLING_QTY,TOTAL_NET_WEIGHT = @TOTAL_NET_WEIGHT,TOTAL_NET_VOLUME = @TOTAL_NET_VOLUME,REMARK= @REMARK,BILLING_AMOUNT = @BILLING_AMOUNT WHERE ACCESSORIAL_CHARGES_ID = @ACCESSORIAL_CHARGES_ID  ";
        private string SQL_UPDATE_ACCESSORIAL_CHARGES_IS_CHECKED = " UPDATE @_@ACCESSORIAL_CHARGES SET   IS_CHECKED = 'Y' WHERE ACCESSORIAL_CHARGES_ID = @ACCESSORIAL_CHARGES_ID  ";
        private string TAX_GROUP_ID = "@TAX_GROUP_ID";
        private string TOTAL_NET_VOLUME = "@TOTAL_NET_VOLUME";
        private string TOTAL_NET_WEIGHT = "@TOTAL_NET_WEIGHT";
        private string TS = "@TS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckAccessorialChargesIDUnique(string accessorialChargesID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ACCESSORIAL_CHARGES_ID", accessorialChargesID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_ACCESSORIAL_CHARGES_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckAccessorialChargesIDUnique(DataBase dataBase, DbTransaction tran, string accessorialChargesID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ACCESSORIAL_CHARGES_ID", accessorialChargesID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_ACCESSORIAL_CHARGES_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteAccessorialCharges(List<string> accessorialChargesID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < accessorialChargesID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ACCESSORIAL_CHARGES_ID", accessorialChargesID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteAccessorialCharges(DataBase dataBase, DbTransaction tran, List<string> accessorialChargesID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < accessorialChargesID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ACCESSORIAL_CHARGES_ID", accessorialChargesID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public List<AccessorialChargesInfo> GetAccessorialChargesByID(string orderID, string accessorialType, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID) + " WHERE ORDER_ID = @ORDER_ID AND ACCESSORIAL_TYPE =@ACCESSORIAL_TYPE  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ACCESSORIAL_TYPE", accessorialType) };
            List<AccessorialChargesInfo> accessorialChargesInfoList = new List<AccessorialChargesInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    AccessorialChargesInfo accessorialChargesInfo = new AccessorialChargesInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ACCESSORIAL_CHARGES_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["RATE_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), (reader["DEBIT"].ToString() != "") ? decimal.Parse(reader["DEBIT"].ToString()) : 0M, (reader["BILLED_UNITS"].ToString() != "") ? decimal.Parse(reader["BILLED_UNITS"].ToString()) : 0M, reader["ACCESSORIAL_ID"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["CHARGE_UNIT"].ToString(), (reader["CHARGE_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["CHARGE_PER_UNIT"].ToString()) : 0M, reader["CHARGE_INVOCATION"].ToString(), reader["ACCESSORIAL_TYPE"].ToString(), reader["DESCR"].ToString(), reader["IS_CHECKED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["COST_TYPE"].ToString(), reader["BILLING_OBJECT"].ToString(), reader["BILLING_OBJECT_DESCR"].ToString(), reader["BILLING_OBJECT_ID"].ToString(), reader["BILLING_OBJECT_NAME"].ToString(), reader["DUE_FROM_TO"].ToString(), decimal.Parse(reader["BILLING_QTY"].ToString()), decimal.Parse(reader["TOTAL_NET_WEIGHT"].ToString()), decimal.Parse(reader["TOTAL_NET_VOLUME"].ToString()), reader["REMARK"].ToString(), reader["CHARGE_TYPE"].ToString(), decimal.Parse(reader["BILLING_AMOUNT"].ToString()));
                    accessorialChargesInfoList.Add(accessorialChargesInfo);
                }
            }
            return accessorialChargesInfoList;
        }

        public AccessorialChargesInfo GetAccessorialChargesByID(string orderID, string orderLineID, string accessorialType, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID) + " WHERE ORDER_ID = @ORDER_ID AND  ORDER_LINE_ID=@ORDER_LINE_ID  AND ACCESSORIAL_TYPE = @ACCESSORIAL_TYPE  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", orderLineID), new SqlParameter("@ACCESSORIAL_TYPE", accessorialType) };
            AccessorialChargesInfo accessorialChargesInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    accessorialChargesInfo = new AccessorialChargesInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ACCESSORIAL_CHARGES_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["RATE_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), (reader["DEBIT"].ToString() != "") ? decimal.Parse(reader["DEBIT"].ToString()) : 0M, (reader["BILLED_UNITS"].ToString() != "") ? decimal.Parse(reader["BILLED_UNITS"].ToString()) : 0M, reader["ACCESSORIAL_ID"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["CHARGE_UNIT"].ToString(), (reader["CHARGE_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["CHARGE_PER_UNIT"].ToString()) : 0M, reader["CHARGE_INVOCATION"].ToString(), reader["ACCESSORIAL_TYPE"].ToString(), reader["DESCR"].ToString(), reader["IS_CHECKED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["COST_TYPE"].ToString(), reader["BILLING_OBJECT"].ToString(), reader["BILLING_OBJECT_DESCR"].ToString(), reader["BILLING_OBJECT_ID"].ToString(), reader["BILLING_OBJECT_NAME"].ToString(), reader["DUE_FROM_TO"].ToString(), decimal.Parse(reader["BILLING_QTY"].ToString()), decimal.Parse(reader["TOTAL_NET_WEIGHT"].ToString()), decimal.Parse(reader["TOTAL_NET_VOLUME"].ToString()), reader["REMARK"].ToString(), reader["CHARGE_TYPE"].ToString(), decimal.Parse(reader["BILLING_AMOUNT"].ToString()));
                }
            }
            return accessorialChargesInfo;
        }

        public AccessorialChargesInfo GetAccessorialChargesByID(DataBase dataBase, DbTransaction tran, string accessorialchargesID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID) + " WHERE ACCESSORIAL_CHARGES_ID = @ACCESSORIAL_CHARGES_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ACCESSORIAL_CHARGES_ID", accessorialchargesID) };
            AccessorialChargesInfo accessorialChargesInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                accessorialChargesInfo = new AccessorialChargesInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ACCESSORIAL_CHARGES_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["RATE_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), (reader["DEBIT"].ToString() != "") ? decimal.Parse(reader["DEBIT"].ToString()) : 0M, (reader["BILLED_UNITS"].ToString() != "") ? decimal.Parse(reader["BILLED_UNITS"].ToString()) : 0M, reader["ACCESSORIAL_ID"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["CHARGE_UNIT"].ToString(), (reader["CHARGE_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["CHARGE_PER_UNIT"].ToString()) : 0M, reader["CHARGE_INVOCATION"].ToString(), reader["ACCESSORIAL_TYPE"].ToString(), reader["DESCR"].ToString(), reader["IS_CHECKED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["COST_TYPE"].ToString(), reader["BILLING_OBJECT"].ToString(), reader["BILLING_OBJECT_DESCR"].ToString(), reader["BILLING_OBJECT_ID"].ToString(), reader["BILLING_OBJECT_NAME"].ToString(), reader["DUE_FROM_TO"].ToString(), decimal.Parse(reader["BILLING_QTY"].ToString()), decimal.Parse(reader["TOTAL_NET_WEIGHT"].ToString()), decimal.Parse(reader["TOTAL_NET_VOLUME"].ToString()), reader["REMARK"].ToString(), reader["CHARGE_TYPE"].ToString(), decimal.Parse(reader["BILLING_AMOUNT"].ToString()));
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return accessorialChargesInfo;
        }

        public List<AccessorialChargesInfo> GetAccessorialChargesByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID) + " WHERE ORDER_ID=@ORDER_ID AND IS_CHECKED='N' ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            List<AccessorialChargesInfo> accessorialChargesInfoList = new List<AccessorialChargesInfo>();
            try
            {
                IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras);
                while (reader.Read())
                {
                    DateTime CS00002;
                    AccessorialChargesInfo accessorialChargesInfo = new AccessorialChargesInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ACCESSORIAL_CHARGES_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["RATE_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), (reader["DEBIT"].ToString() != "") ? decimal.Parse(reader["DEBIT"].ToString()) : 0M, (reader["BILLED_UNITS"].ToString() != "") ? decimal.Parse(reader["BILLED_UNITS"].ToString()) : 0M, reader["ACCESSORIAL_ID"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["CHARGE_UNIT"].ToString(), (reader["CHARGE_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["CHARGE_PER_UNIT"].ToString()) : 0M, reader["CHARGE_INVOCATION"].ToString(), reader["ACCESSORIAL_TYPE"].ToString(), reader["DESCR"].ToString(), reader["IS_CHECKED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["COST_TYPE"].ToString(), reader["BILLING_OBJECT"].ToString(), reader["BILLING_OBJECT_DESCR"].ToString(), reader["BILLING_OBJECT_ID"].ToString(), reader["BILLING_OBJECT_NAME"].ToString(), reader["DUE_FROM_TO"].ToString(), decimal.Parse(reader["BILLING_QTY"].ToString()), decimal.Parse(reader["TOTAL_NET_WEIGHT"].ToString()), decimal.Parse(reader["TOTAL_NET_VOLUME"].ToString()), reader["REMARK"].ToString(), reader["CHARGE_TYPE"].ToString(), decimal.Parse(reader["BILLING_AMOUNT"].ToString()));
                    accessorialChargesInfoList.Add(accessorialChargesInfo);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return accessorialChargesInfoList;
        }

        public DataSet GetAccessorialChargesByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, AccessorialChargesQueryEntity accessorialchargesQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!accessorialchargesQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, accessorialchargesQuery.CurrentPage, accessorialchargesQuery.PageSize, accessorialchargesQuery.SortField, accessorialchargesQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public AccessorialChargesInfo GetAccessorialChargesByWhere(string where, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID).Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + where;
            AccessorialChargesInfo accessorialChargesInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql))
            {
                if (reader.Read())
                {
                    accessorialChargesInfo = new AccessorialChargesInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ACCESSORIAL_CHARGES_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["RATE_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), (reader["DEBIT"].ToString() != "") ? decimal.Parse(reader["DEBIT"].ToString()) : 0M, (reader["BILLED_UNITS"].ToString() != "") ? decimal.Parse(reader["BILLED_UNITS"].ToString()) : 0M, reader["ACCESSORIAL_ID"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["CHARGE_UNIT"].ToString(), (reader["CHARGE_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["CHARGE_PER_UNIT"].ToString()) : 0M, reader["CHARGE_INVOCATION"].ToString(), reader["ACCESSORIAL_TYPE"].ToString(), reader["DESCR"].ToString(), reader["IS_CHECKED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["COST_TYPE"].ToString(), reader["BILLING_OBJECT"].ToString(), reader["BILLING_OBJECT_DESCR"].ToString(), reader["BILLING_OBJECT_ID"].ToString(), reader["BILLING_OBJECT_NAME"].ToString(), reader["DUE_FROM_TO"].ToString(), decimal.Parse(reader["BILLING_QTY"].ToString()), decimal.Parse(reader["TOTAL_NET_WEIGHT"].ToString()), decimal.Parse(reader["TOTAL_NET_VOLUME"].ToString()), reader["REMARK"].ToString(), reader["CHARGE_TYPE"].ToString(), decimal.Parse(reader["BILLING_AMOUNT"].ToString()));
                }
            }
            return accessorialChargesInfo;
        }

        public DataSet GetAccessorialChargesInBoundByQueryList(List<string> sqlWhere, AccessorialChargesQueryEntity accessorialchargesQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_IN_BOUND.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!accessorialchargesQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, accessorialchargesQuery.CurrentPage, accessorialchargesQuery.PageSize, accessorialchargesQuery.SortField, accessorialchargesQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public AccessorialChargesOrderInfo GetAccessorialChargesInBoundOrderByID(string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_IN_BOUND.Replace("@_@", whLoginID).Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + "  WHERE R.RECEIPT_ID = @RECEIPT_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", orderID) };
            AccessorialChargesOrderInfo accessorialChargesOrderInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    accessorialChargesOrderInfo = new AccessorialChargesOrderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), "", reader["STATUS"].ToString(), reader["TYPE"].ToString());
                }
            }
            return accessorialChargesOrderInfo;
        }

        public List<AccessorialChargesOrderDetailInfo> GetAccessorialChargesInBoundOrderDetailByID(string accessorialchargesID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID).Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + "WHERE RD.RECEIPT_ID = @RECEIPT_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", accessorialchargesID) };
            List<AccessorialChargesOrderDetailInfo> accessorialChargesInfoList = new List<AccessorialChargesOrderDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    AccessorialChargesOrderDetailInfo accessorialChargesInfo = new AccessorialChargesOrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), Convert.ToInt32(reader["RECEIPT_LINE_ID"]), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["TO_LOT"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), Convert.ToDecimal(reader["QTY"]));
                    accessorialChargesInfoList.Add(accessorialChargesInfo);
                }
            }
            return accessorialChargesInfoList;
        }

        public AccessorialChargesOrderInfo GetAccessorialChargesMovementByID(string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_MOVEMENT.Replace("@_@", whLoginID).Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + "  WHERE R.MOVEMENT_ID = @MOVEMENT_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", orderID) };
            AccessorialChargesOrderInfo accessorialChargesOrderInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    accessorialChargesOrderInfo = new AccessorialChargesOrderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), "", reader["STATUS"].ToString(), reader["TYPE"].ToString());
                }
            }
            return accessorialChargesOrderInfo;
        }

        public DataSet GetAccessorialChargesMovementByQueryList(List<string> sqlWhere, AccessorialChargesQueryEntity accessorialchargesQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_MOVEMENT.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!accessorialchargesQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, accessorialchargesQuery.CurrentPage, accessorialchargesQuery.PageSize, accessorialchargesQuery.SortField, accessorialchargesQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public List<AccessorialChargesOrderDetailInfo> GetAccessorialChargesMovementDetailByID(string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_MOVEMENT_DETAIL.Replace("@_@", whLoginID).Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + "WHERE RD.MOVEMENT_ID = @MOVEMENT_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", orderID) };
            List<AccessorialChargesOrderDetailInfo> accessorialChargesInfoList = new List<AccessorialChargesOrderDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    AccessorialChargesOrderDetailInfo accessorialChargesInfo = new AccessorialChargesOrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), Convert.ToInt32(reader["LINE_ID"]), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LOT"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), Convert.ToDecimal(reader["QTY"]));
                    accessorialChargesInfoList.Add(accessorialChargesInfo);
                }
            }
            return accessorialChargesInfoList;
        }

        public DataSet GetAccessorialChargesOutBoundByQueryList(List<string> sqlWhere, AccessorialChargesQueryEntity accessorialchargesQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_OUT_BOUND.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!accessorialchargesQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, accessorialchargesQuery.CurrentPage, accessorialchargesQuery.PageSize, accessorialchargesQuery.SortField, accessorialchargesQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public AccessorialChargesOrderInfo GetAccessorialChargesOutBoundOrderByID(string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_OUT_BOUND.Replace("@_@", whLoginID).Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + "  WHERE R.ORDER_ID = @ORDER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            AccessorialChargesOrderInfo accessorialChargesOrderInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    accessorialChargesOrderInfo = new AccessorialChargesOrderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), "", reader["STATUS"].ToString(), reader["TYPE"].ToString());
                }
            }
            return accessorialChargesOrderInfo;
        }

        public List<AccessorialChargesOrderDetailInfo> GetAccessorialChargesOutBoundOrderDetailByID(string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA.Replace("@_@", whLoginID).Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + "WHERE RD.ORDER_ID = @ORDER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            List<AccessorialChargesOrderDetailInfo> accessorialChargesInfoList = new List<AccessorialChargesOrderDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    AccessorialChargesOrderDetailInfo accessorialChargesInfo = new AccessorialChargesOrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), Convert.ToInt32(reader["LINE_ID"]), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LOT"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), Convert.ToDecimal(reader["QTY"]));
                    accessorialChargesInfoList.Add(accessorialChargesInfo);
                }
            }
            return accessorialChargesInfoList;
        }

        public DataSet GetAllAccessorialCharges(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllAccessorialCharges(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public int InsertAccessorialCharges(AccessorialChargesInfo accessorialChargesInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                accessorialChargesInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_AccessorialCharges_Parameters(accessorialChargesInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertAccessorialCharges(DataBase dataBase, DbTransaction tran, AccessorialChargesInfo accessorialChargesInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                accessorialChargesInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_AccessorialCharges_Parameters(accessorialChargesInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_AccessorialCharges_Parameters(AccessorialChargesInfo accessorialChargesInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.ACCESSORIAL_CHARGES_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 20), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.LOT, SqlDbType.VarChar, 20), new SqlParameter(this.RATE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.GENERAL_LEDGER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DEBIT, SqlDbType.Decimal, 13), new SqlParameter(this.BILLED_UNITS, SqlDbType.Decimal, 13), new SqlParameter(this.ACCESSORIAL_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TAX_GROUP_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CHARGE_UNIT, SqlDbType.VarChar, 20), new SqlParameter(this.CHARGE_PER_UNIT, SqlDbType.Decimal, 13), 
                new SqlParameter(this.CHARGE_INVOCATION, SqlDbType.VarChar, 20), new SqlParameter(this.ACCESSORIAL_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.DESCR, SqlDbType.VarChar, 100), new SqlParameter(this.IS_CHECKED, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.COST_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.BILLING_OBJECT, SqlDbType.VarChar, 20), new SqlParameter(this.BILLING_OBJECT_ID, SqlDbType.VarChar, 30), new SqlParameter(this.BILLING_OBJECT_NAME, SqlDbType.VarChar, 20), new SqlParameter(this.DUE_FROM_TO, SqlDbType.VarChar, 20), new SqlParameter(this.BILLING_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.TOTAL_NET_WEIGHT, SqlDbType.Decimal, 13), new SqlParameter(this.TOTAL_NET_VOLUME, SqlDbType.Decimal, 13), new SqlParameter(this.REMARK, SqlDbType.VarChar, 100), new SqlParameter(this.CHARGE_TYPE, SqlDbType.VarChar, 20), 
                new SqlParameter(this.BILLING_AMOUNT, SqlDbType.Decimal, 13)
             };
            if (!string.IsNullOrEmpty(accessorialChargesInfo.WhID))
            {
                paramArray[0].Value = accessorialChargesInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = accessorialChargesInfo.AccessorialChargesID;
            if (!string.IsNullOrEmpty(accessorialChargesInfo.OrderID))
            {
                paramArray[2].Value = accessorialChargesInfo.OrderID;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            paramArray[3].Value = accessorialChargesInfo.OrderLineID;
            if (!string.IsNullOrEmpty(accessorialChargesInfo.OwnerID))
            {
                paramArray[4].Value = accessorialChargesInfo.OwnerID;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            paramArray[5].Value = accessorialChargesInfo.SkuID;
            paramArray[6].Value = accessorialChargesInfo.Qty;
            if (!string.IsNullOrEmpty(accessorialChargesInfo.Lot))
            {
                paramArray[7].Value = accessorialChargesInfo.Lot;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            paramArray[8].Value = accessorialChargesInfo.RateID;
            if (!string.IsNullOrEmpty(accessorialChargesInfo.GeneralLedgerID))
            {
                paramArray[9].Value = accessorialChargesInfo.GeneralLedgerID;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            paramArray[10].Value = accessorialChargesInfo.Debit;
            paramArray[11].Value = accessorialChargesInfo.BilledUnits;
            if (!string.IsNullOrEmpty(accessorialChargesInfo.AccessorialID))
            {
                paramArray[12].Value = accessorialChargesInfo.AccessorialID;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.TaxGroupID))
            {
                paramArray[13].Value = accessorialChargesInfo.TaxGroupID;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.ChargeUnit))
            {
                paramArray[14].Value = accessorialChargesInfo.ChargeUnit;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            paramArray[15].Value = accessorialChargesInfo.ChargePerUnit;
            if (!string.IsNullOrEmpty(accessorialChargesInfo.ChargeInvocation))
            {
                paramArray[0x10].Value = accessorialChargesInfo.ChargeInvocation;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.AccessorialType))
            {
                paramArray[0x11].Value = accessorialChargesInfo.AccessorialType;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.Descr))
            {
                paramArray[0x12].Value = accessorialChargesInfo.Descr;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.IsChecked))
            {
                paramArray[0x13].Value = accessorialChargesInfo.IsChecked;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            paramArray[20].Value = accessorialChargesInfo.CreatedBy;
            paramArray[0x15].Value = accessorialChargesInfo.UpdatedBy;
            if (!string.IsNullOrEmpty(accessorialChargesInfo.CostType))
            {
                paramArray[0x16].Value = accessorialChargesInfo.CostType;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.BillingObject))
            {
                paramArray[0x17].Value = accessorialChargesInfo.BillingObject;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.BillingObjectID))
            {
                paramArray[0x18].Value = accessorialChargesInfo.BillingObjectID;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.BillingObjectName))
            {
                paramArray[0x19].Value = accessorialChargesInfo.BillingObjectName;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.DueFromTo))
            {
                paramArray[0x1a].Value = accessorialChargesInfo.DueFromTo;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.BillingQty.ToString()))
            {
                paramArray[0x1b].Value = accessorialChargesInfo.BillingQty;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.TotalNetWeight.ToString()))
            {
                paramArray[0x1c].Value = accessorialChargesInfo.TotalNetWeight;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.TotalNetVolume.ToString()))
            {
                paramArray[0x1d].Value = accessorialChargesInfo.TotalNetVolume;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.Remark))
            {
                paramArray[30].Value = accessorialChargesInfo.Remark;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.ChargeType))
            {
                paramArray[0x1f].Value = accessorialChargesInfo.ChargeType;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(accessorialChargesInfo.BillingAmount.ToString()))
            {
                paramArray[0x20].Value = accessorialChargesInfo.BillingAmount;
                return paramArray;
            }
            paramArray[0x20].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateAccessorialCharges(AccessorialChargesInfo accessorialChargesInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                accessorialChargesInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_AccessorialCharges_Parameters(accessorialChargesInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateAccessorialCharges(DataBase dataBase, DbTransaction tran, AccessorialChargesInfo accessorialChargesInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                accessorialChargesInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_AccessorialCharges_Parameters(accessorialChargesInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ACCESSORIAL_CHARGES.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateAccessorialChargesIsChecked(DataBase dataBase, DbTransaction tran, string AccessorialChargesID, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ACCESSORIAL_CHARGES_ID", AccessorialChargesID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ACCESSORIAL_CHARGES_IS_CHECKED.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

